package io.gitee.macxiang.sql;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Service;

import io.gitee.macxiang.entity.SqlFieldMap;
import io.gitee.macxiang.utils.utils;

import javax.sql.DataSource;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Base64;
import java.util.HashMap;
import java.util.List;

/** 数据库操作服务 */
@Service
public class DatabaseService {

  private DataSource dataSource;

  @Autowired
  public DatabaseService(@Qualifier("sqlConfig") SqlConfig sqlConfig) {
    this.dataSource = sqlConfig.dataSource();
  }

  @Autowired
  @Qualifier("sqlConfig")
  SqlConfig sqlConfig;

  /** 切换数据源
   * @param database    数据库库名
   * @param addr        数据库地址
   * @param port        数据库端口
   */
  public void changeDataSource(String database, String addr, Integer port) {
    if (!utils.isEmpty(database)) {
      this.dataSource = sqlConfig.dataSource(database, addr, port);
    }
  }

  /** 初始化字段映射
   * @param list      字段列表
   * @param tableName 表名
   */
  public void initSqlFieldMap(List<SqlFieldMap> list, String tableName) {
    try (Connection connection = dataSource.getConnection();
        Statement statement = connection.createStatement();
        ResultSet res = statement.executeQuery(String.format("select * from %s limit 0", tableName));) {
      ResultSetMetaData md = res.getMetaData();
      list.forEach(e -> {
        try {
          int column = res.findColumn(e.sqlNameSimple);
          if (column >= 0) {
            switch (md.getColumnType(column)) {
              case Types.BINARY:
              case Types.VARBINARY:
              case Types.LONGVARBINARY:
              case Types.BLOB:
              case Types.ARRAY:
                e.isString = false;
                break;
              default:
                e.isString = true;
            }
          }
        } catch (Exception e1) {
          if (!e.select) {
            System.err.println(String.format("错误: 初始化表[%s]字段:%s\t%s", tableName, e.sqlName, utils.errString(e1)));
            utils.out(e1);
          }
        }
      });
    } catch (Exception e) {
      System.err.println("错误: 初始化SqlFieldMap 连接数据库");
      utils.out(e);
    }
  }

  /** 创建实体
   * @param <T>       实体类泛型
   * @param res       结果集
   * @param clazz     实体类
   * @param fieldMap  字段映射表
   * @return          实体
   */
  private <T> T createEntity(ResultSet res, Class<T> clazz, List<SqlFieldMap> fieldMap) {
    try {
      T ret = clazz.getDeclaredConstructor().newInstance();
      for (SqlFieldMap e : fieldMap) {
        try {
          String sqlName = e.sqlNameSimple;
          Object vd = utils.parseJavaObject(
              e.isString
                  ? res.getString(sqlName)
                  : Base64.getEncoder().encodeToString(res.getBytes(sqlName)),
              e.field.getType());
          e.field.set(ret, vd);
        } catch (Exception e2) {
        }
      }
      return ret;
    } catch (Exception e) {
      System.err.println("创建实体错误2");
      utils.out(e);
      return null;
    }
  }

  /** 执行select语句
   * @param sql         查询语句
   * @param dataSource  数据源; 默认yml配置的原
   * @return            返回HashMap格式列表
   */
  public List<HashMap<String, Object>> selectList(String sql, DataSource dataSource) {
    if (dataSource == null) {
      dataSource = this.dataSource;
    }
    List<HashMap<String, Object>> ret = new ArrayList<>();
    try (Connection connection = dataSource.getConnection();
        Statement statement = connection.createStatement();
        ResultSet res = statement.executeQuery(sql);) {
      ResultSetMetaData metaData = res.getMetaData();
      int columnCount = metaData.getColumnCount();

      while (res.next()) {
        HashMap<String, Object> row = new HashMap<>();
        for (int i = 1; i <= columnCount; i++) {
          String columnName = utils.UnderlineHump(metaData.getColumnLabel(i));
          Object columnValue = res.getObject(i);
          row.put(columnName, columnValue);
        }
        ret.add(row);
      }
    } catch (SQLException e) {
      utils.out(e);
    }
    return ret;
  }

  /** 执行select语句
   * @param sql         查询语句
   * @return            返回HashMap格式列表
   */
  public List<HashMap<String, Object>> selectList(String sql) {
    return selectList(sql, dataSource);
  }

  /** 查询列表数据
   * @param <T>         泛型
   * @param sql         sql语句
   * @param clazz       实体类
   * @param map         字段映射
   * @param dataSource  数据源
   * @return            查询到的List
   */
  public <T> List<T> selectList(String sql, Class<T> clazz, List<SqlFieldMap> map, DataSource dataSource) {
    if (dataSource == null) {
      dataSource = this.dataSource;
    }
    List<T> ret = new ArrayList<>();
    if (map != null) {
      try (Connection connection = dataSource.getConnection();
          Statement statement = connection.createStatement();
          ResultSet res = statement.executeQuery(sql);) {
        while (res.next()) {
          try {
            ret.add(createEntity(res, clazz, map));
          } catch (Exception e) {
          }
        }
      } catch (SQLException e) {
        utils.out(e);
      }
    }
    return ret;
  }

  public <T> List<T> selectList(String sql, Class<T> clazz, List<SqlFieldMap> map) {
    return selectList(sql, clazz, map, dataSource);
  }

  /** 查询一条数据
   * @param <T>         泛型
   * @param sql         sql语句
   * @param clazz       实体类
   * @param map         字段映射
   * @param dataSource  数据源
   * @return            查询到的实体
   */
  public <T> T selectOne(String sql, Class<T> clazz, List<SqlFieldMap> map, DataSource dataSource) {
    if (dataSource == null) {
      dataSource = this.dataSource;
    }
    if (!sql.matches("(?i).* limit\\s+[0-9,]+$")) {
      sql += " LIMIT 0,1";
    }
    try (Connection connection = dataSource.getConnection();
        Statement statement = connection.createStatement();
        ResultSet res = statement.executeQuery(sql);) {
      return res.next() ? createEntity(res, clazz, map) : null;
    } catch (Exception e) {
      utils.out(e);
    }
    return null;
  }

  /** 查询一条数据
   * @param <T>   泛型
   * @param sql   sql语句
   * @param clazz 实体类
   * @param map   字段映射
   * @return      查询到的实体
   */
  public <T> T selectOne(String sql, Class<T> clazz, List<SqlFieldMap> map) {
    return selectOne(sql, clazz, map, dataSource);
  }

  /** 执行保存语句
   * @param sql           保存sql语句
   * @param dataSource    数据源
   * @return              保存数量
   */
  public int save(String sql, DataSource dataSource) {
    if (dataSource == null) {
      dataSource = this.dataSource;
    }
    int ret = 0;
    Connection con = null;
    Exception re = null;
    try {
      con = dataSource.getConnection();
      con.setAutoCommit(false);
      Statement statement = con.createStatement();
      ret = statement.executeUpdate(sql);
      statement.close();
      con.commit();
    } catch (Exception e) {
      re = e;
      if (con != null) {
        try {
          con.rollback();
        } catch (SQLException e1) {
        }
      }
    } finally {
      if (con != null) {
        try {
          con.setAutoCommit(true);
          con.close();
        } catch (Exception e) {
        }
      }
    }
    if (re != null) {
      utils.out(re);
    }
    return ret;
  }

  /** 执行保存语句
   * @param sql           保存sql语句
   * @return              保存数量
   */
  public int save(String sql) {
    return save(sql, dataSource);
  }

  /** 获取数据表行数,与count()不同的是,直接运行sql语句,不进行预处理,且语句里必须包含count(*)
   * @param sql         sql语句
   * @param dataSource  数据源
   * @return            行数
   */
  public int countReal(String sql, DataSource dataSource) {
    if (dataSource == null) {
      dataSource = this.dataSource;
    }
    int ret = 0;
    try (Connection connection = dataSource.getConnection();
        Statement statement = connection.createStatement();
        ResultSet res = statement.executeQuery(sql);) {
      res.next();
      ret = res.getInt(1);
    } catch (Exception e) {
      utils.out(e);
    }
    return ret;
  }

  /** 获取数据表行数,与count()不同的是,直接运行sql语句,不进行预处理,且语句里必须包含count(*)
   * @param sql         sql语句
   * @return            行数
   */
  public int countReal(String sql) {
    return countReal(sql, dataSource);
  }

  /** 获取数据总数
   * @param sql 查询的sql语句
   * @param dataSource 数据源
   * @return    数据总数
   */
  public int count(String sql, DataSource dataSource) {
    if (!sql.matches("(?i)^SELECT\\s+COUNT\\([\\s\\*]+\\).*")) {
      sql = String.format("SELECT COUNT(*) FROM (%s) AS subquery", sql);
    }
    return countReal(sql, dataSource);
  }

  /** 获取数据总数
   * @param sql 查询的sql语句
   * @return    数据总数
   */
  public int count(String sql) {
    return count(sql, dataSource);
  }

}